package cn.java86;
//import com.mysql.cj.jdbc.Driver;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;
import java.util.Scanner;

/**
 * @author oldliu
 * @since 1.0
 */
public class Jdbc5 {
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return;
        }
        //用QQ号，模糊查找
        Scanner in=new Scanner(System.in);
        System.out.println("input QQ:");
        String qq = in.next();
        System.out.println("一页几条?");
        int pageSize=in.nextInt();
        System.out.println("想看第几页？");
        int pageNo=in.nextInt();
        //?参数
        String url = "jdbc:mysql://localhost:3308/java86school?useUnicode=true&characterEncoding=utf8&useSSL=false" +
                "&serverTimezone=GMT%2B8";
        String user = "root";
        String pass = "root1234";
        Connection con = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            con = DriverManager.getConnection(url, user, pass);

            // String sql = "select * from s_student where qq like '%'||?||'%'";
            String sql = "select * from s_student where qq like concat('%',?,'%') limit ?,?";

            st = con.prepareStatement(sql);
            //从1开始,给占位符填充值
            st.setString(1, qq);
            st.setInt(2, (pageNo - 1) * pageSize);
            st.setInt(3, pageSize);
            rs = st.executeQuery();
            while (rs.next()) {
                long stuno = rs.getLong("stuno");
                String name = rs.getString("sname");
                String wechat = rs.getString("wechat");

                Date enter = rs.getDate("enterdate");
                System.out.println(stuno + " " + name + " " + wechat + " " + enter);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (Exception e) {
                //e.printStackTrace();
            }
            try {
                if (st != null)
                    st.close();
            } catch (Exception e) {
                //e.printStackTrace();
            }
            try {
                con.close();
            } catch (Exception e) {
                //e.printStackTrace();
            }
        }
    }
}
